Examples of Macros

Info

For the procedures below, you can use the buttons to help you entering the macro scripts. If you are an advanced user, you can also enter the script directly in the Script field.

This example shows the function of Discrepancy without cumulation.

Goal: The sales of product A (VENA budget type), of which the amounts were entered in the Operational Budget, must be automatically collected in the Treasury Budget with a one-month discrepancy and a VAT of 19.6%.

You can set up macro for the VENA code. The correct result is the following:

Operational Budget Jan. Feb. Mar. Apr.
VENA 100 200 100 0
Treasury Budget Jan. Feb. Mar. Apr.
VENA 0 119.6 239.2 119.6

Method: In the Liquidity module, you need to define a script of a one-month discrepancy for the amounts and the specified budget code.

  1. Create a macro linked to a specific budget code and in the Script area, click the Macros Wizard button.

View more details about the Creation of a Macro

  1. In the Wizard, select Discrepancy in the Function Selection area.

View more details on the Discrepancy function

  1. In the section Elements of function, select the same budget code as on the creation page of the macro.
  2. Enter 100 in the Percentage area to apply the discrepancy to the whole amount.
  3. In the Start Date area, keep the value DATE.
  4. To define a discrepancy of one month, in the End Date field, enter DATE.
    Click the button , select Arithmetical Operators, then Summation Sign. Place the cursor in the entry field after "DATE+" and enter 1. Click the button, select Period Steps, then Month.
    The following text is displayed:
    DATE+1M
  1. Click OK at the top of the page to validate the script.
    The creation page of the macro is displayed again with the script in the Script area.
    The pattern of the final script is the following:
    DISC(ACHA;100%;DATE;DATE+1M)
  2. Click OK to save the macro.

This example shows the function of Discrepancy with cumulation.

Goal: If your equipment purchases (ACHM budget type) can be paid every three months, the script to create and its impact on your budgets can be summed up in the following way:

Operational Budget Jan. Feb. Mar.
ACHM 10 10 10
Treasury Budget Jan. Feb. Mar.
ACHM=DISC(ACHM;100%;DATE;ENDOFQUARTER) 0 0 30

Method: In the Liquidity module, you need to create a script with a discrepancy applying to 100% of the amounts of these equipment purchases and deferring to the end of the quarter all the amounts created for this budget code since the beginning of the quarter.

  1. Create a macro linked to a specific budget code and in the Script area, click the Macros Wizard button.

View more details about the Creation of a Macro

  1. In the Wizard, select Discrepancy in the Function Selection area.

View more details on the Discrepancy function

  1. In the section Elements of function, select the same budget code as on the creation page of the macro.
  2. Enter 100 in the Percentage area to apply the discrepancy to the whole amount.
  3. In the Start Date area, keep the value DATE.
  4. In the End Date field, click the button, select Period Steps, then End of Quarter to defer to the end of the quarter the amounts created until the end of the quarter.
    The following text is displayed:
    ENDOFQUARTER
  5. Click OK at the top of the page to validate the script.
    The creation page of the macro is displayed again with the script in the Script area.
    The pattern of the final script is the following:
    DISC(ACHM;100%;DATE;ENDOFQUARTER)
  6. Click OK to save the macro.

This example shows the function of Discrepancy which enables the definition of an accurate discrepancy "30 days at the end of the month, the..." between the Operational Budget and the Treasury Budget.

The wizard enables you to:

  • set up the number of discrepancy months,
  • post the result in the Treasury within a given period (1st ten days, fortnight... for example),
  • apply a discrepancy to a percentage or to the whole amount.

Goal: The sales of product A are collected at 30 days at the end of the month, the tenth day. The following tables illustrate the follow-up of the budgets:

Operational Budget 1st jan. 2nd Jan. 3rd Jan.
VENA 10 20 30
Treasury Budget 1st jan. 2nd Jan. 3rd Jan. 10 Mar.
VENA       70

Method: In the Liquidity module, you need to define a discrepancy script.

  1. Create a macro linked to a specific budget code and in the Script area, click the Macros Wizard button.

View more details about the Creation of a Macro

  1. In the Wizard, select Discrepancy in the Function Selection area.

View more details on the Discrepancy function

  1. In the section Elements of function, select the same budget code as on the creation page of the macro.
  2. Enter 100 in the Percentage area to apply the discrepancy to the whole amount.
  3. To set the collection discrepancy to one month, place the cursor in the Start date field, after the word DATE.
    Click the button , select Arithmetical Operators, then Summation Sign. Place the cursor in the entry field after "DATE+" and enter 1. Click the button, select Period Steps, then Month.
    The following text is displayed:
    DATE+1M
  4. To define the payment date, click the button in the End Date field, select Period Steps, then End of Month.
    Click the button , select Arithmetical Operators, then Summation Sign. Place the cursor in the entry field after "ENDOFMONTH+ " and enter 1. Click the button select Period Steps, then 10 days.
    The following text is displayed:
    ENDOFMONTH+1T
  1. Click OK at the top of the page to validate the script.
    The creation page of the macro is displayed again with the script in the Script area.
    The pattern of the final script is the following:
    DISC(VENA;100%;DATE+1M;ENDOFMONTH+1T) 
  2. Click OK to save the macro.

In some cases, when an element of Treasury is to be calculated, a correspondence between this element and certain elements of the Treasury Budget (and not of the Operational Budget) must be established.

Goal: In this example, you created the VTFR code in your Operational Budget, which corresponds to the total sales of products A, B and C.

You want a summation in the Treasury for the values of products A, B and C of which the collection is deferred in the Treasury compared with the Operational Budget.

The VTR code must be defined in the Treasury based on each of the A, B and C parts of the Treasury.

As the A sales collection is immediate, that the B sales collection is in 30 days and that C sales collection is in 60 days, your budgets will mention the following:

Operational Budget Jan. Feb. Mar.
VENA 100    
VENB 100    
VENC 100    
Treasury Budget Jan. Feb. Mar.
VENA 100    
VENB   100  
VENC     100
VTFR=VENA@+VENB@+VENC@ 100 100 100

Method: In the Liquidity module, in order to calculate the VTFR code, you need to create a script which enables you to add 3 amounts in the Operational Budget, of the VENA, VENB and VENC budget types.

When setting up the budget design, you will apply the VTFR macro only in the Treasury Budget.

For more information on the setup of the deferred collection for the VENA, VENB and VENC codes, see the first example of this topic on the deferred discrepancy of sales.

  1. Create a macro linked to the required budget code and place your cursor in the Script entry field to enter the script.
    The selected budget code must match the sales total.

View more details about the Creation of a Macro

  1. Click the button Budget Code and, in the contextual window, select the first budget code to include in the addition.
    The code of the selected budget type is displayed in the script entry line.
  2. Click the button Arithmetical Operators and, in the contextual window, select Reference to TBS.
    The at-sign (@) is displayed next the code of the budget type and means that the reference amount belongs to the Treasury Budget.
  3. Click the button Arithmetical Operators and, in the contextual window, select Summation Sign.
    The + sign is added in the entry script.
  4. Click the button Budget Code and, in the contextual window, select the second budget code to include in the addition.
    The code of the selected budget type is added to the script entry line.
  5. Click the button Arithmetical Operators and, in the contextual window, select Reference to TBS.
    The at-sign (@) is displayed next the code of the budget type and means that the reference amount belongs to the Treasury Budget.
  6. Redo steps 4 to 6 as many times as necessary to add budget codes to the addition.
    The pattern of the final script is the following:
    VENA@+VENB@+VENC@
  7. Validate the script and save.

View more details

This example shows the proportionality that can exist between two budget codes.

Goal: The Fringe Benefits (FBEN budget code) represent 42% of the Salaries (SALARY budget code).

In order to avoid entering the fringe benefits, you can set up a macro for the FBEN code. The correct result is the following:

Operational Budget Jan. Feb. Mar.
SALARY 100 100 100
FBEN=SALARY*42% 42 42 42

Method: In the Liquidity module, you need to define a script for the calculation of Fringe Benefits according to the salary, so that the Fringe Benefits equal 42% of the salary.

  1. Create a macro linked to the required budget code.
    In this example, we are talking about fringe benefits.

View more details about the Creation of a Macro

  1. In the Script field, click the Budget Code button and select the budget type corresponding to the salary.
    The code of the selected budget type is displayed in the script entry line.
  2. Click the Arithmetical Operators button and select Multiplication Sign.
    The * sign is added in the entry script.
  3. In the entry field, place your cursor at the end of the line and enter the value of the fringe benefits part in percentage compared with the salaries.
    The number entered is added in the entry script.
  4. Click the Arithmetical Operators button and select Percentage Sign.
  5. The % sign is added in the entry script.
    The pattern of the final script displayed in the entry field is the following:
    SALARY*42%
  6. Validate the script and save.

View more details

This example shows the function of Discrepancy with cumulation. This function enables you to perform a calculation on amounts of different periods at a given frequency.

Goal: The Quarterly Fringe Benefits represent 10% of all the Fringe Benefits in the Operational Budget.

The quarterly payments in the Treasury correspond to the summation of the fringe benefits defined in the Operational budget on the three months preceding the payment.

  • The FBEN code corresponds to the overall fringe benefits.
  • The QUBEN code corresponds to the quarterly fringe benefits.

The follow-up table for the budgets shows the following information:

Operational Budget Jan. Feb. Mar. Apr.
CHS 100 100 100 100
CHTR=CHS*10% 10 10 10 10
Treasury Budget Jan. Feb. Mar. Apr.
CHTR=DISC(CHTR;100%;DATE;ENDOFQUARTER+1D)       30

Method: In the Liquidity module, you need to specify two macros.

The first one applies to the Operational Budget and specifies that the quarterly benefits represent 10% of the fringe benefits amount. The second one applies to the Treasury Budget and specifies that the quarterly benefits are cumulated at the end of the quarter.

In the design of your budget sheet, you need to apply the first macro to the Operational Budget and the second one to the Treasury Budget.

  1. Create a macro linked to the required budget code.
    In this example, we are talking about the budget code linked to the quarterly benefits.

View more details about the Creation of a Macro

  1. In the Script field, click the Budget Code button and select the budget type corresponding to the fringe benefits.
    The code of the selected budget type is displayed in the script entry line.
  2. Click the Arithmetical Operators button and select Multiplication Sign.
    The * sign is added in the entry script.
  3. In the entry field, place your cursor at the end of the line and enter the value of the quarterly benefits part in percentage compared with the fringe benefits.
    The number entered is added in the entry script.
  4. Click the Arithmetical Operators button and select Percentage Sign.
  5. The % sign is added in the entry script.
    The pattern of the final script displayed in the entry field is the following:
    FBEN*10%
  6. Validate the script and save.

View more details

  1. Create a macro linked to a specific budget code and in the Script area, click the Macros Wizard button.
    In this example, we are talking about the budget code linked to the quarterly benefits.

View more details about the Creation of a Macro

  1. In the Wizard, select Discrepancy in the Function Selection area.

View more details on the Discrepancy function

  1. In the section Elements of function, select the same budget code as on the creation page of the macro.
  2. Enter 100 in the Percentage area to apply the discrepancy to the whole amount.
  3. In the Start Date area, keep the value DATE.
  4. To define the payment date, click the button in the End Date field, select Period Steps, then End of Quarter.
    Click the button , select Arithmetical Operators, then Summation Sign. Place the cursor in the entry field after "ENDOFQUARTER+ " and enter 1. Click the button, select Period Steps, then Day.
    The following text is displayed:
    ENDOFQUARTER+1D
  1. Click OK at the top of the page to validate the script.
    The creation page of the macro is displayed again with the script in the Script area.
    The pattern of the final script is the following:
    DISC(CHTR;100%;DATE;ENDOFQUARTER+1D) 
  2. Click OK to save the macro.

This example shows the breakdown on the Treasury Budget of the operating data with a Periodic Distribution Law.

Goal: In this example, the sales of product B must be collected in the following way:

  • 0% in the current month,
  • 60% in 30 days,
  • 30% in 60 days,
  • 10% in 90 days.

With a monthly display of the budget, the correct result is:

Operational Budget Jan. Feb. Mar. Apr.
VENB 100      
Treasury Budget Jan. Feb. Mar. Apr.
VENB=
PDISTR(VENB;DATE;(1M:60%);(2M:30%);(3M:10%)		 0 60 30 10

Method: In the Liquidity module, you need to create a script with a Periodic Distribution of the sales amount over three months.

  1. Create a macro linked to a specific budget code and in the Script area, click the Macros Wizard button.

View more details about the Creation of a Macro

  1. In the Wizard, select Periodic Distribution in the Function Selection area.

View more details about the Periodic Distribution

  1. In the section Elements of function, select the same budget code as on the creation page of the macro.
  2. In the Start Date area, click the button , select Period Steps, then Day to start the distribution from the transaction date.
    The following text is displayed:
    DATE
  3. In the first line of the following table, enter "1M" in the Period column and 60 in the Percentage colunmn to defer 60% of the amount to the next month.
  4. Click the button to add a line to the table.
  5. Enter "2M" in the Period column and 30 in the Percentage column to defer 30% of the amount to the next month.
  6. Redo steps 6 and 7 as many times as necessary to add periods to the distribution.
Info

You can decide not to break down 100% of the amount.

  1. Click OK at the top of the page to validate the script.
    The creation page of the macro is displayed again with the script in the Script area.
    The pattern of the final script is the following:
    PDISTR(VENB;DATE;(1M:60%);(2M:30%);(3M:10%))
  2. Click OK to save the macro.

Cet exemple illustre une relation arithmétique entre postes au sein d'un même budget.

Goal: Your turnover (TU budget code) is determined by the addition of the sales of product A (VENA ) and the sales of product B (VENB).

You can set up macro for the turnover. The correct result is the following:

Operational Budget Jan. Feb. Mar.
VENA 100 120 80
VENB 30 50 35
CA = VENA + VENB 130 170 115

Method: In the Liquidity module, you need to set up a system budget code which corresponds to the turnover, then you need to create a macro with the addition of two other budget codes.

Info

Setting up the turnover as a system budget code avoids the double posting of amounts in the Treasury Budget.

For more information on creating system budget codes, see topic Codification of the System Budget Codes.

  1. Create a macro linked to the budget type corresponding to the total.

View more details about the Creation of a Macro

  1. In the Script area, click the Budget Codes button and select the budget type to include in the addition.
    The code of the selected budget type is displayed in the script entry line.
  2. Click the Arithmetical Operators button and select Summation Sign.
    The + sign is added in the entry script.
  3. In the Script area, click the Budget Code button and select the second budget type to include in the addition.
    The code of the selected budget type is added in the script entry line.
  4. Redo the previous steps as many times as necessary to add budget codes to the addition.
    The pattern of the final script is the following:
    VENA+VENB
  5. Validate the script and save.

View more details

This example shows the function of Discrepancy with cumulation using the Periodicity.

Goal: If your electricity consumption is calculated on a monthly basis but is payable on a two-month basis, the script to create and its impact on your budgets can be summed up by the following:

Operational Budget Jan. Feb. Mar. Apr.
ELEC 100 100 100 100
Treasury Budget Jan. Feb. Mar. Apr.
ELEC=DISC(ELEC;100%;DATE;ENDOFMONTH;2) 0 200   200

Method: In the Liquidity module, you need to create a script with a discrepancy over 100% of the amount with cumulation at the end of the month every two months.

  1. Create a macro linked to a specific budget code and in the Script area, click the Macros Wizard button.

View more details about the Creation of a Macro

  1. In the Wizard, select Discrepancy in the Function Selection area.

View more details on the Discrepancy function

  1. In the section Elements of function, select the same budget code as on the creation page of the macro.
  2. Enter 100 in the Percentage area to apply the discrepancy to the whole amount.
  3. In the Start Date area, keep the value DATE.
  4. In the End Date area, click the button , select Period Steps, then End of Month to defer the amount to the end of the month.
    The following text is displayed:
    ENDOFMONTH
  5. In the Periodicity field, enter 2 to total the amounts every two months.
  6. Click OK at the top of the page to validate the script.
    The creation page of the macro is displayed again with the script in the Script area.
    The pattern of the final script is the following:
    DISC(ELEC;100%;DATE;ENDOFMONTH;2)
  7. Click OK to save the macro.